﻿using NPOI.HSSF.UserModel;
using NPOI.POIFS.Crypt.Dsig;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml.Linq;
using 日铁.Modle;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;

namespace 日铁
{
    public partial class AnnualBudgetList : Form
    {

        Class1 one = new Class1();
        public AnnualBudgetList(string DJBH="")
        {
            InitializeComponent();

            textBox1.Text = DJBH;
            this.WindowState = FormWindowState.Maximized;
            label1.TextAlign = ContentAlignment.TopCenter;
            this.FormClosing += new FormClosingEventHandler(Form1_FormClosing);

        }
        private void AnnualBudgetList_Load(object sender, EventArgs e)
        {
            Listname();
            string DJBH = textBox1.Text;
            if (DJBH == "")
            {
                string year = DateTime.Now.Year.ToString();
                textBox1.Text = GET_DJSL();
                textBox3.Text = "创建";
                textBox2.Text = year;
            }
            else
            {
                Get_ndysd(DJBH);
            }
        }
        private void 保存ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                string Fnumber = textBox1.Text;
                string Fstatus = textBox3.Text;
                string Fyear = textBox2.Text;
                string Fctime = dateTimePicker1.Value.ToString("yyyy-MM-dd");
                string Ffounder = textBox4.Text;
                string Fatime = "1900-01-01";
                string Fauditor = textBox5.Text;


                using (SqlConnection connection = DBHelper.CreateConnection())
                {
                    string selectcount = $"select * from T_Annual_budget_list where Fyear='{Fyear}' ";
                    DataTable dataTable= DBHelper.ExecuteDataTable(selectcount, connection, false, null);
                    int row = dataTable.Rows.Count;
                    string insertText = $"insert into T_Annual_budget_list values('{Fnumber}','{Fstatus}','{Fyear}','{Fctime}','{Ffounder}','{Fatime}','{Fauditor}')";
                    int rows = DBHelper.ExecuteNonQuery(insertText, connection, false, null);

                    string FID = DBHelper.ExecuteScalar($"select ABIID from T_Annual_budget_list where Fnumber = '{Fnumber}'", connection, false, null).ToString();

                    foreach (DataGridViewRow item in dataGridView1.Rows)
                    {
                        string Fbtype = "";
                        string Fmpurchase = "";
                        string FNSCH = "";
                        string FMAterialID = "";
                        string FMname = "";
                        string FSAM = "";
                        string Funit = "";
                        string FIBA = "";
                        string FIBQ = "";
                        string FRBA = "";
                        string FRBQ = "";

                        object Fbtype2 = item.Cells[0].Value;
                        object Fmpurchase2 = item.Cells[1].Value;
                        object FNSCH2 = item.Cells[2].Value;
                        object FMAterialID2 = item.Cells[3].Value;
                        object FMname2 = item.Cells[4].Value;
                        object FSAM2 = item.Cells[5].Value;
                        object Funit2 = item.Cells[6].Value;
                        object FIBA2 = item.Cells[7].Value;
                        object FIBQ2 = item.Cells[8].Value;
                        object FRBA2 = item.Cells[9].Value;
                        object FRBQ2 = item.Cells[10].Value;
                        if (Fbtype2 != null)
                        {
                            Fbtype = Fbtype2.ToString();
                        }
                        if (Fmpurchase2 != null)
                        {
                            Fmpurchase = Fmpurchase2.ToString();
                        }
                        if (FNSCH2 != null)
                        {
                            FNSCH = FNSCH2.ToString();
                        }
                        if (FMAterialID2 != null)
                        {
                            FMAterialID = FMAterialID2.ToString();
                        }
                        if (FMname2 != null)
                        {
                            FMname = FMname2.ToString();
                        }
                        if (FSAM2 != null)
                        {
                            FSAM = FSAM2.ToString();
                        }
                        if (Funit2 != null)
                        {
                            Funit = Funit2.ToString();
                        }
                        if (FIBA2 != null)
                        {
                            FIBA = FIBA2.ToString();
                        }
                        if (FIBQ2 != null)
                        {
                            FIBQ = FIBQ2.ToString();
                        }
                        if (FRBA2 != null)
                        {
                            FRBA = FRBA2.ToString();
                        }
                        if (FIBQ2 != null)
                        {
                            FRBQ = FRBQ2.ToString();
                        }

                        if ((FMAterialID == "" && Fbtype == "固定资产")|| FMAterialID != "")
                        {
                            int ro = DBHelper.ExecuteNonQuery($"INSERT INTO T_Annual_budget_list_E (FID, Fbtype, Fmpurchase,FNSCH,FMAterialID,FMname,FSAM,Funit,FIBA,FIBQ,FRBA,FRBQ) VALUES ({FID}, '{Fbtype}', '{Fmpurchase}','{FNSCH}','{FMAterialID}','{FMname}','{FSAM}','{Funit}',{FIBA},{FIBQ},{FRBA},{FRBQ})", connection, false, null);
                        }
                        
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            MessageBox.Show("保存成功");
        }
        private void 审核ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            
            try
            {
                string DJBH = textBox1.Text;
                string Fatime = DateTime.Now.ToString("yyyy-MM-dd");
                using (SqlConnection connection = DBHelper.CreateConnection())
                {
                    string insertText = $"update T_Annual_budget_list set Fstatus='审核',Fatime='{Fatime}' where Fnumber='{DJBH}'";
                    int rows = DBHelper.ExecuteNonQuery(insertText, connection, false, null);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            MessageBox.Show("审核成功");
        }
        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                string DJBH = textBox1.Text;

                using (SqlConnection connection = DBHelper.CreateConnection())
                {
                    string picture = DBHelper.ExecuteScalar($"select ABIID from T_Annual_budget_list  WHERE Fnumber='{DJBH}'", connection, false, null).ToString();

                    string DELETEable = $"DELETE T_Annual_budget_list_E WHERE FID='{picture}'";
                    int rows2 = DBHelper.ExecuteNonQuery(DELETEable, connection, false, null);

                    string DELETEabl = $"DELETE T_Annual_budget_list WHERE ABIID='{picture}'";
                    int rows = DBHelper.ExecuteNonQuery(DELETEabl, connection, false, null);

                    
                }
                this.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        

        private void 导出ToolStripMenuItem_Click(object sender, EventArgs e)
        {

            // 创建一个 FolderBrowserDialog 实例  
            using (FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog())
            {
                // 设置对话框的标题  
                folderBrowserDialog.Description = "请选择一个文件夹";

                // 显示对话框并检查结果  
                if (folderBrowserDialog.ShowDialog() == DialogResult.OK)
                {
                    // 获取用户选择的文件夹路径  
                    string selectedFolderPath = folderBrowserDialog.SelectedPath;

                    // 在文本框中显示路径  
                    OpenFileWithWPS(selectedFolderPath);
                }
            }
            
        }


        private void 导入ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            // 实例化OpenFileDialog  
            OpenFileDialog openFileDialog = new OpenFileDialog();
            string filePath = "";

            // 显示对话框  
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                // 用户点击了确定，获取文件路径  
                filePath = openFileDialog.FileName;
            }

            ExcelFilePath(filePath);
        }

        private void OpenFileWithWPS(string filePath)
        {
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
            using (ExcelPackage excel = new ExcelPackage())
            {
                // 添加工作表
                var worksheet = excel.Workbook.Worksheets.Add("Sheet1");
                // 写入数据
                worksheet.Cells[1, 1].Value = "预算类型";
                worksheet.Cells[1, 2].Value = "采购方式";
                worksheet.Cells[1, 3].Value = "NSCH采购类型";
                worksheet.Cells[1, 4].Value = "物料代码";
                worksheet.Cells[1, 5].Value = "物料名称";
                worksheet.Cells[1, 6].Value = "规格型号";
                worksheet.Cells[1, 7].Value = "单位";
                worksheet.Cells[1, 8].Value = "年度预算金额";
                worksheet.Cells[1, 9].Value = "年度预算数量";
                worksheet.Cells[1, 10].Value = "年度预算调整金额";
                worksheet.Cells[1, 11].Value = "年度预算调整数量";
                int i = 2;
                foreach (DataGridViewRow item in dataGridView1.Rows)
                {
                    if (item.Cells[0].Value == null)
                    {
                        item.Cells[0].Value = "";
                    }
                    if (item.Cells[1].Value == null)
                    {
                        item.Cells[1].Value = "";
                    }
                    if (item.Cells[2].Value == null)
                    {
                        item.Cells[2].Value = "";
                    }
                    if (item.Cells[3].Value == null)
                    {
                        item.Cells[3].Value = "";
                    }
                    if (item.Cells[4].Value == null)
                    {
                        item.Cells[4].Value = "";
                    }
                    if (item.Cells[5].Value == null)
                    {
                        item.Cells[5].Value = "";
                    }
                    if (item.Cells[6].Value == null)
                    {
                        item.Cells[6].Value = "";
                    }
                    if (item.Cells[7].Value == null)
                    {
                        item.Cells[7].Value = "";
                    }
                    if (item.Cells[8].Value == null)
                    {
                        item.Cells[8].Value = "";
                    }
                    if (item.Cells[9].Value == null)
                    {
                        item.Cells[9].Value = "";
                    }
                    if (item.Cells[10].Value == null)
                    {
                        item.Cells[10].Value = "";
                    }
                    int index = 1;
                    worksheet.Cells[i, index].Value = item.Cells[0].Value.ToString(); ;
                    index++;
                    worksheet.Cells[i, index].Value = item.Cells[1].Value.ToString(); ;
                    index++;
                    worksheet.Cells[i, index].Value = item.Cells[2].Value.ToString(); ;
                    index++;
                    worksheet.Cells[i, index].Value = item.Cells[3].Value.ToString(); ;
                    index++;
                    worksheet.Cells[i, index].Value = item.Cells[4].Value.ToString(); ;
                    index++;
                    worksheet.Cells[i, index].Value = item.Cells[5].Value.ToString(); ;
                    index++;
                    worksheet.Cells[i, index].Value = item.Cells[6].Value.ToString();
                    index++;
                    worksheet.Cells[i, index].Value = item.Cells[7].Value.ToString(); ;
                    index++;
                    worksheet.Cells[i, index].Value = item.Cells[8].Value.ToString(); ;
                    index++;
                    worksheet.Cells[i, index].Value = item.Cells[9].Value.ToString(); ;
                    index++;
                    worksheet.Cells[i, index].Value = item.Cells[10].Value.ToString(); ;
                    i++;

                }
                worksheet.Cells["A:Z"].AutoFitColumns();
                string path = "";
               
                string Name = filePath + "年度预算单.xlsx";
                // 确保目录存在  
                Directory.CreateDirectory(filePath);

                // 生成唯一的文件名（这里只是简单示例，你可能需要更复杂的唯一性逻辑）  
                string fileName = Path.GetFileNameWithoutExtension(Name) + "_" + Guid.NewGuid() + Path.GetExtension(Name);

                // 保存文件到服务器  
                string filePaths = Path.Combine(filePath, fileName);
                excel.SaveAs(filePaths);
            }
        }

        public void ExcelFilePath(string physicalPath1)
        {
            System.Data.DataTable table = ExcelToDatatable(physicalPath1, "Sheet1", true);

            //自定义一个datatable
            var table1 = new System.Data.DataTable();
            table1.Columns.Add("Fbtype", typeof(string));
            table1.Columns.Add("Fmpurchase", typeof(string));
            table1.Columns.Add("FNSCH", typeof(string));
            table1.Columns.Add("FMAterialID", typeof(string));
            table1.Columns.Add("FMname", typeof(string));
            table1.Columns.Add("FSAM", typeof(string));
            table1.Columns.Add("Funit", typeof(string));
            table1.Columns.Add("FIBA", typeof(string));
            table1.Columns.Add("FIBQ", typeof(string));
            table1.Columns.Add("FRBA", typeof(string));
            table1.Columns.Add("FRBQ", typeof(string));

            int temp = 0;
            //去除空值
            foreach (DataRow row in table.Rows)
            {
                using (SqlConnection connection = DBHelper.CreateConnection())
                {
                    string FMAterialID = row[3].ToString();
                    string Fbtype = row[2].ToString();

                    if (Fbtype == "固定资产")
                    {
                        table1.Rows.Add(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[7], row[8]);
                    }
                    else
                    {
                        string selectcount = $"select * from t_icitem where FNumber='{FMAterialID}' ";
                        DataTable dataTable = DBHelper.ExecuteDataTable(selectcount, connection, false, null);
                        int rows = dataTable.Rows.Count;
                        if (rows > 0)
                        {
                            table1.Rows.Add(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[7], row[8]);
                        }
                    }
                }
            }
            dataGridView1.DataSource = table1;
        }

        public static System.Data.DataTable ExcelToDatatable(string fileName, string sheetName, bool isFirstRowColumn)
        {
            System.Data.DataTable data = new System.Data.DataTable();
            int startRow = 0;
            int cellCount = 0;//列数
            int rowCount = 0;//行数
            try
            {
                using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    // 创建HSSFWorkbook或XSSFWorkbook对象，取决于你的Excel文件格式  
                    IWorkbook workbook;
                    if (System.IO.Path.GetExtension(fileName).Equals(".xls"))
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(fs);
                    }

                    // 获取第一个工作表  
                    ISheet sheet = workbook.GetSheetAt(0);
                    if (sheet != null)
                    {
                        IRow firstRow = sheet.GetRow(0);
                        cellCount = firstRow.LastCellNum; //第一行最后一个cell的编号 即总的列数
                        if (isFirstRowColumn)//如果第一行是标题行
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)//第一行列数循环
                            {
                                DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);//获取标题
                                data.Columns.Add(column);//添加列
                            }
                            startRow = sheet.FirstRowNum + 1;//1（即第二行，第一行0从开始）
                        }
                        else
                        {
                            startRow = sheet.FirstRowNum;//0
                        }
                        //最后一行的标号
                        rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)//循环遍历所有行
                        {
                            IRow row = sheet.GetRow(i);//第几行
                            if (row != null)
                            {
                                //将excel表每一行的数据添加到datatable的行中
                                DataRow dataRow = data.NewRow();
                                for (int j = row.FirstCellNum; j < cellCount; ++j)
                                {
                                    if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                    {
                                        dataRow[j] = row.GetCell(j).ToString();
                                    }
                                }
                                data.Rows.Add(dataRow);
                            }
                            else
                            {
                                continue; //没有数据的行默认是null;
                            }

                        }
                    }
                    return data;
                }
            }
            catch (Exception EX)
            {
                MessageBox.Show(EX.ToString());
                return data;
            }
        }

        

        private string GET_DJSL()
        {
            string Now = DateTime.Now.ToString("yyyy-MM-dd");
            SqlConnection connection = DBHelper.CreateConnection();
            DataTable dataTable = DBHelper.ExecuteDataTable($"select * from T_Annual_budget_list", connection, false, null);
            int rows = dataTable.Rows.Count;

            string DHBM = "NDYSD" + rows + 1 + Now;
            return DHBM;
        }
        private void Get_ndysd(string DDBH)
        {
            try
            {
                string DJBH = textBox1.Text;
                int id = 0;
                using (SqlConnection connection = DBHelper.CreateConnection())
                {
                    DataTable dataTable = DBHelper.ExecuteDataTable($"select * from T_Annual_budget_list where Fnumber='{DDBH}'", connection, false, null);
                    if (dataTable.Rows.Count > 0)
                    {
                        foreach (DataRow row in dataTable.Rows)
                        {
                            id= (int)row["ABIID"];
                            textBox1.Text = row["Fnumber"].ToString();
                            textBox3.Text = row["Fstatus"].ToString();
                            textBox2.Text = row["Fyear"].ToString();
                            dateTimePicker1.Value = DateTime.Parse(row["Fctime"].ToString());
                            textBox4.Text = row["Ffounder"].ToString();
                            dateTimePicker2.Value = DateTime.Parse(row["Fatime"].ToString());
                            textBox5.Text = row["Fauditor"].ToString();
                        }
                    }
                    if (id != 0)
                    {
                        DataTable data = one.DAtaijs();
                        DataTable dataTable2 = DBHelper.ExecuteDataTable($"select * from T_Annual_budget_list_E where FID='{id}'", connection, false, null);
                        foreach (DataRow row2 in dataTable2.Rows)
                        {
                            data.Rows.Add(row2.ItemArray[2].ToString(), row2.ItemArray[3].ToString(), row2.ItemArray[4].ToString(), row2.ItemArray[5].ToString(), row2.ItemArray[6].ToString(), row2.ItemArray[7].ToString(), row2.ItemArray[8].ToString(), row2.ItemArray[9].ToString(), row2.ItemArray[10].ToString(), row2.ItemArray[11].ToString(), row2.ItemArray[12].ToString());
                        }
                        dataGridView1.DataSource = data;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Listname()
        {
            

            DataGridViewTextBoxColumn Fbtype = new DataGridViewTextBoxColumn();
            Fbtype.HeaderText = "预算类型";
            Fbtype.DataPropertyName = "Fbtype";
            dataGridView1.Columns.Add(Fbtype);

            DataGridViewTextBoxColumn Fmpurchase = new DataGridViewTextBoxColumn();
            Fmpurchase.HeaderText = "采购方式";
            Fmpurchase.DataPropertyName = "Fmpurchase";
            dataGridView1.Columns.Add(Fmpurchase);

            DataGridViewTextBoxColumn FNSCH = new DataGridViewTextBoxColumn();
            FNSCH.HeaderText = "NSCH采购类型";
            FNSCH.DataPropertyName = "FNSCH";
            dataGridView1.Columns.Add(FNSCH);

            DataGridViewTextBoxColumn FMAterialID = new DataGridViewTextBoxColumn();
            FMAterialID.HeaderText = "物料代码";
            FMAterialID.DataPropertyName = "FMAterialID";
            dataGridView1.Columns.Add(FMAterialID);

            DataGridViewTextBoxColumn FMname = new DataGridViewTextBoxColumn();
            FMname.HeaderText = "物料名称";
            FMname.DataPropertyName = "FMname";
            dataGridView1.Columns.Add(FMname);

            DataGridViewTextBoxColumn FSAM = new DataGridViewTextBoxColumn();
            FSAM.HeaderText = "规格型号";
            FSAM.DataPropertyName = "FSAM";
            dataGridView1.Columns.Add(FSAM);

            DataGridViewTextBoxColumn Funit = new DataGridViewTextBoxColumn();
            Funit.HeaderText = "单位";
            Funit.DataPropertyName = "Funit";
            dataGridView1.Columns.Add(Funit);

            DataGridViewTextBoxColumn FIBA = new DataGridViewTextBoxColumn();
            FIBA.HeaderText = "年度预算金额";
            FIBA.DataPropertyName = "FIBA";
            dataGridView1.Columns.Add(FIBA);

            DataGridViewTextBoxColumn FIBQ = new DataGridViewTextBoxColumn();
            FIBQ.HeaderText = "年度预算数量";
            FIBQ.DataPropertyName = "FIBQ";
            dataGridView1.Columns.Add(FIBQ);

            DataGridViewTextBoxColumn FRBA = new DataGridViewTextBoxColumn();
            FRBA.HeaderText = "年度预算调整金额";
            FRBA.DataPropertyName = "FRBA";
            dataGridView1.Columns.Add(FRBA);

            DataGridViewTextBoxColumn FRBQ = new DataGridViewTextBoxColumn();
            FRBQ.HeaderText = "年度预算调整数量";
            FRBQ.DataPropertyName = "FRBQ";
            dataGridView1.Columns.Add(FRBQ);

            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
        }


        private void splitContainer1_Panel1_Paint(object sender, PaintEventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {

        }

        private void label8_Click(object sender, EventArgs e)
        {

        }
        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            // 这里可以添加一些自定义逻辑，比如询问用户是否确定要关闭窗体  
            DialogResult result = MessageBox.Show("你确定要关闭这个窗体吗？", "确认关闭", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

            if (result == DialogResult.No)
            {
                // 如果用户选择“否”，则取消关闭操作  
                e.Cancel = true;
            }
            else
            {
                // 如果用户选择“是”，可以在这里执行一些清理操作  
                // 例如，保存数据、释放资源等  
                // 这里可以添加你的清理代码  
                DetailedBudgetBist longk = new DetailedBudgetBist();
                longk.WindowState = FormWindowState.Normal;
                longk.Show();
            }
        }
    }
}
